<?php
	class  Default_Model_DbTable_Cv extends Zend_Db_Table_Abstract
	{
	    protected $_name = 'cv';
	    protected $_sequence = '_seq_cv';

	    protected $_primary = 'cv_id';
	    protected $_pk = 'cv_id';

        public function addRow($data)
        {

            $this->insert($data);
            $id = $this->_db->lastInsertId($this->_name); //var_dump($newId);
            return $id;
        }

        public function deleteRow($id)
        {

            //$this->update(array('is_deleted'=>'1','is_active'=>'0'), $this->_pk . ' = ' . (int)$id);

            $this->delete($this->_pk .'=' . (int)$id);
            //$this->delete($this->_primary .'=' . (int)$id);
            //$this->delete($this->select()->where($this->_pk . ' = ?', (int)$id));
        }

        public function getRows($filterArr, $orderArr=array("cv_update_date" => "DESC"), $returnArray = false, $withPagination = false, $itemsPerPage=100, $currentPageNumber=1, $getTotals = false,$pageRange=19)
        {

            //if (empty($filterArr)) {
            //throw new Exception("No filter is specified");
            //}

            if ($getTotals === true) {
                $filter = $this->select()->from($this->_name, 'count(institution_id) as total');
            } else {
                $filter = $this->select();
            }
            if (!empty($filterArr)) {
                foreach ($filterArr as $field_id=>$field_value) {
                    if (is_array($field_value)) {//print_r($field_value);exit;
                        foreach ($field_value as $subFilter) { //var_dump($subFilter);exit;
                            if (!empty($subFilter['value'])) {
                                if ($subFilter['operator'] == 'IN') {
                                    $placeHolder = '(?)';
                                } else {
                                    $placeHolder = '?';
                                }
                                $filter->where($field_id . ' '.$subFilter['operator'].' '.$placeHolder, $subFilter['value']);
                            }
                        }
                    } else {
                        $field_value = strip_tags(trim($field_value));
                        if ($field_id == 'keyword') {
                            if (!empty($field_value)) {
                                //$filter->orWhere("institution_label_en LIKE ?", "%{$field_value}%");
                                //$filter->orWhere("erasmus_code LIKE ?", "%{$field_value}%");
                                $filter->Where("institution_label_en LIKE '%".$field_value."%' OR erasmus_code LIKE '".$field_value."'");
                                //institution_label_en LIKE '%teknik%' OR erasmus_code LIKE '%teknik%'
                            }
                        } else {
                            if (!empty($field_value)) {
                                $filter->where($field_id . ' = ?', $field_value);
                            }
                        }
                    }
                }
            }
            if (!empty($orderArr)) {
                foreach ($orderArr as $field_id=>$orderType) {
                    $filter->order($field_id .' '. $orderType);
                }
            }
            //echo $filter;exit;
            if ($withPagination === true) {
                $paginator = Zend_Paginator::factory($filter);
                $paginator->setPageRange($pageRange);
                $paginator->setItemCountPerPage($itemsPerPage);
                $paginator->setCurrentPageNumber($currentPageNumber);
                return $paginator;

            } else if ($getTotals === true) {
                $total = $this->_db->fetchOne($filter);
                return $total;
            } else {
                $rows = $this->fetchAll($filter);
            }




            if (!$rows) {
                return array();
                //throw new Exception("Could not find rows");
            }
            if ($returnArray == true) {
                return $rows->toArray();
            }

            return $rows;

        }


	    public function getUser($id, $returnArray = false)
	    {
	        $id = (int)$id;
	    	$row = $this->fetchRow($this->_pk .'= '. $id);
	        if (!$row) {
	            throw new Exception("Count not find user with id: $id");
	        }
	        //return $row->toArray();//to return it as array
	        if ($returnArray == true) {
		        return $row->toArray();
	        }
	        return $row;//return as it is-> it is by default an object

	    }

	    public function getUserByActivationCode($code)
	    {
	    	$row = $this->fetchRow(
			    $this->select()->where('activation_code = ?', $code)
		    );
	        //CALISMIYOR//$row = $this->fetchRow('activation_code= '. $code);
	        if (!$row) {
	            //throw new Exception("Count not find row with activation code: ".$code."");
  	            return false;

	        }
	        return $row;
	    }

	    public function getUserByEmail($email,$rowCount=false)
	    {

	    	$row = $this->fetchRow(
			    $this->select()->where('usermail = ?', $email)
		    );
            if($rowCount==true){
                $row = count($row);
            }
	        return $row;
	    }

        public function getUserByNationalIdNumber($nationalIdNumber,$rowCount=false)
        {

            $row = $this->fetchRow(
                $this->select()->where('national_id_number = ?', $nationalIdNumber)
            );
            if($rowCount==true){
                $row = count($row);
            }
            return $row;
        }
/*
	    public function getSequence()
	    {
	        $seq = $this->nextSequenceId();
	        if (!$seq) {
	            throw new Exception("Count not get seq");
	        }
	        return $seq;
	    }
*/

	    public function findUsers($filterArr, $orderArr = array(), $returnArray = false)
	    {

	        $filter = $this->select();
	        if (!empty($filterArr)) {
		        foreach ($filterArr as $field_id=>$field_value) {
			    	$filter->where($field_id . ' = ?', $field_value);
		        }
	        }
	        if (!empty($orderArr)) {
		        foreach ($orderArr as $field_id=>$orderType) {
			    	$filter->order($field_id .' '. $orderType);
		        }
	        }
            //echo $filter;exit;

			$rowsTemp = $this->fetchAll($filter);
	        if (!$rowsTemp) {
	            throw new Exception("Could not find rows");
	        }
	        foreach ($rowsTemp as $key=>$values) {
				$rows[$values->{$this->_pk}] = (object)$values->toArray();
	        }
	        //print_r($rows);exit;
	        unset($rowsTemp);
			return $rows;

	    }

	    public function findUsersForAutocompleteSearch($queryString, $orderArr = array(), $returnArray = false)
	    {

	    	$queryString = trim($queryString);
	    	if (empty($queryString) || (strlen($queryString) < 3)) {
	    		return null;
	    	}

			$where1 = $this->_db->quoteInto('firstname LIKE ?', $queryString.'%');
			$where2 = $this->_db->quoteInto('lastname LIKE ?', $queryString.'%');
			$where3 = $this->_db->quoteInto('is_active = ?', '1');

			$filterStr =  'SELECT user_id, firstname, lastname, usermail FROM '.$this->_name.' WHERE ( '.$where1 .' OR '.$where2 .') AND ('. $where3 .') ORDER BY firstname ASC, lastname ASC';

			$rows = $this->_db->fetchAll($filterStr);

			return $rows;

	    }



	    public function updateUser($id, $data)
	    {
	        $this->update($data, $this->_pk . ' = ' . (int)$id);
	        //$this->update($data, $this->_primary . ' = ' . (int)$id);

	    }



		public function generateActivationCode($token, $len=15) {

			$token = empty($token) ? uniqid() : $token;
			$activation_code = substr(md5($token . time() . uniqid()), 0, $len);
			return $activation_code;

		}

		public function generatePassword($token, $len=8) {

			$token = empty($token) ? uniqid() : $token;
			$password = substr(md5($token . time() . uniqid()), 10, $len);
			return $password;

		}

		public function sendActivationCodeMail($user) {

			try {

				if (empty($user)) {
					throw new Exception("empty/invalid user data");
				}
                //$model = new Default_Model_DbTable_User();
                //$user = $model->getUser($userID);

                $activation = new stdClass();
                $activation->activationCode = $user->activation_code;
                $activation->email = $user->usermail;

				if (empty($activation)) {
					throw new Exception("Count not find row user activation data");
				}

				$siteConfig = Zend_Registry::get('siteConfig');
                $mailer = new Default_Model_Mailer();
                $mailView = $mailer->getMailView();


				$mailView->activation = $activation;
				$bodyText  = $mailView->render('signup.phtml');


				$mailData['to'] 		= $user->usermail;
				$mailData['toLabel'] 	= $user->firstname .' '. $user->lastname;
				$mailData['from'] 		= $siteConfig->emails->from->email;
				$mailData['fromLabel'] 	= $siteConfig->emails->from->label;
				$mailData['subject'] 	= 'Yeni Üyelik: Etkinleştirme kodu';
				$mailData['body'] 		= $bodyText;
				$mailData['mailBodyType'] 	= 'html';

				$mailer->send($mailData);

				$res['res'] = true;
				$res['msg'] = 'activation-mail-sent';



	    	} catch (Exception $e) {
	    		throw new Exception('activation-mail-sending-failed');
				//$res['res'] = true;
				//$res['msg'] = 'activation-mail-sending-failed';
				//$res['error'] = $e->getMessage();
			}
			return $res;

		}
/*
		public function sendUserMail($user, $templateName) {

			$mailer = new Default_Model_Mailer();
			$mailTemplates = $mailer->getMailUserTemplates();
			$mailView = $mailer->mailView();

			$mailTemplateFile = 'user-incoming-student-welcome-mail.phtml';
			$bodyText  = $mailView->render($mailTemplateFile);


			$mailData['to'] 		= $user->usermail;
			$mailData['toLabel'] 	= $user->firstname .' '. $user->lastname;
			$mailData['from'] 		= $siteConfig->emails->from->email;
			$mailData['fromLabel'] 	= $siteConfig->emails->from->label;
			$mailData['subject'] 	= 'Your new password';
			$mailData['body'] 		= $bodyText;
			$mailData['mailBodyType'] 	= 'html';

			$mailer->send($mailData);
		}*/

		public function sendNewUserWelcomeMail($user, $plainPassword, $lang='', $createdByUserId='') {

			try {
				$user = (object)$user;

				if (empty($user->user_id)) {
					throw new Exception("empty-or-invalid-user-id");
				}
				if (empty($user)) {
					throw new Exception("empty-or-invalid-user-data");
				}
				$user->userpass = $plainPassword;

				$siteConfig = Zend_Registry::get('siteConfig');
				$translateObj = Zend_Registry::get('translate');

				$mailer = new Default_Model_Mailer();
				$mailView = $mailer->getMailView();


				if ($user->role=='foreign-institution-not-approved') {
					$mailTemplate = 'foreign-institution-not-approved-new-user-welcome.phtml';
                    $lang = 'en';

				} else if ($user->role=='foreign-institution') {
					$mailTemplate = 'foreign-institution-approved-new-user-welcome.phtml';
                    $lang = 'en';

				} else if ($user->role=='incoming-student') {
					$mailTemplate = 'foreign-student-new-user-welcome.phtml';
                    $lang = 'en';

				} else if ($user->role=='incoming-staff') {
					$mailTemplate = 'incoming-staff-new-user-welcome.phtml';
                    $lang = 'en';

				} else if ($user->role=='member') {
					$mailTemplate = 'signup.phtml';

				} else if ($user->role=='local-coordinator') {
					$mailTemplate = 'signup-coordinator.phtml';

				} else {
					throw new Exception("empty-mail-template-name: userrole: ".$user->role);
				}

				$mailView->user = $user;
				$bodyText  = $mailView->render($mailTemplate);



				$mailData['mailBodyType'] 	= 'html';
				$mailData['subject'] 	= $translateObj->_('new-user-activation-and-welcome-mail', $siteConfig->language->enabledLanguages->{$lang});
				$mailData['from'] 		= $siteConfig->emails->from->email;
				$mailData['fromLabel'] 	= $siteConfig->emails->from->label;
				$mailData['to'] 		= $user->usermail;
				$mailData['toLabel'] 	= $user->firstname .' '. $user->lastname;
				$mailData['body'] 		= $bodyText;
				$mailData['signature_language'] = $lang;
				//$mailData['copyToAdmin'] = true;

				//$mailData['copyToAdmin'] = true;

					/* ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** */
					//ADD SENT MAIL INFORMATION TO DB sent_mails
					$sentMailObj = new Default_Model_DbTable_SentMails();


						$sentMailData['to'] 		= $mailData['to'];
						$sentMailData['toLabel'] 	= $mailData['toLabel'];
						$sentMailData['from'] 		= $mailData['from'];
						$sentMailData['fromLabel'] 	= $mailData['fromLabel'];
						$sentMailData['subject'] 	= $mailData['subject'];
						$sentMailData['body'] 		= $mailData['body'];
						$sentMailData['mailBodyType'] 	= 'html';

						$sentMailData['template'] 	= $mailTemplate;
						$sentMailData['user_id'] 	= $user->user_id;
						$sentMailData['create_user_id'] = $createdByUserId;

						$sentMailId = $sentMailObj->addSentMail($sentMailData);


					/* ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** */

				$mailer->send($mailData);

				$res['res'] = true;
				$res['msg'] = $translateObj->_('new-user-activation-and-welcome-mail-sent');



	    	} catch (Exception $e) {
	    		//throw new Exception('new-user-welcome-mail-sending-failed');
	    		//throw new Exception($e->getMessage());
				$res['res'] = false;
				$res['msg'] = $translateObj->_('new-user-activation-and-welcome-mail-sending-failed');

				$mailerObj = new Default_Model_Mailer();
				$mailerObj->sendAlertMail('activation mail sending failed', 'server: '.php_uname('n').' client: '. CLIENT_NAME .' file: '.__FILE__.' line: '. __LINE__.' ----- Error: '.$e->getMessage());


				//$res['res'] = false;
				//$res['msg'] = 'new-user-welcome-mail-sending-failed';
				//$res['error'] = $e->getMessage();
			}
			return $res;

		}

		public function createUserFromNomination($nominationID, $createdByUserId='', $programType) {


	    	try {

	    		$db = $this->getAdapter();
	    		$db->beginTransaction();

	    		if ( (!$programType) || (!in_array($programType, array('placement', 'erasmus'))) ) {
	    			throw new Exception('empty-or-invalid-nomination-program-type');
	    		}

	        	if (empty($nominationID)) {
					throw new Exception('empty-nomination-id');
				}

				if ($programType == 'placement') {
		            $model = new Default_Model_DbTable_IncomingPlacementNomination();
		            $nomination = $model->getNomination($nominationID, '');

				} else if ($programType == 'erasmus') {
		            $model = new Default_Model_DbTable_IncomingNomination();
		            $nomination = $model->getNomination($nominationID, '');

				}

				$validator = new Zend_Validate_Db_NoRecordExists(array('table' => 'users', 'field' => 'usermail'));
				if (!$validator->isValid($nomination->email_address)) {
					throw new Exception('duplicate-account-with-email');
				}



	            //$newUser = $model->createRow();
	            $newUser->firstname = $nomination->firstname;
	            $newUser->lastname = $nomination->lastname;
	            $newUser->usermail = $nomination->email_address;
	            $newUser->gender_id = $nomination->gender_id;
	            $newUser->is_active = '0';
	            $newUser->is_deleted = '0';
	            $newUser->role = 'incoming-student';
	            $newUser->usertype = 'individual';
	            $newUser->activation_code = $this->generateActivationCode($nomination->email_address);
	            $tmpNewPassword = $this->generatePassword($nomination->email_address);
	            $newUser->userpass = md5($tmpNewPassword);
	            $newUser->create_datetime = date('Y-m-d H:i:s');
	            $newUser->create_ip = $_SERVER['REMOTE_ADDR'];
	            $newUserID = $this->addUser((array)$newUser);

	            $newUser->user_id = $newUserID;
            	$this->sendNewUserWelcomeMail($newUser, $tmpNewPassword, 'en', $createdByUserId);

	            $nomination->account_created='1';
	            $nomination->user_id = $newUserID;
	            $nomination->save();

				$res['res'] = true;
				$res['type'] = 'success';
				$res['msg'] = 'user-account-creation-from-nomination-and-welcome-mail-sending-success-message';
		 		$db->commit();


	    	} catch (Exception $e)
	    	{
	    		$db->rollBack();

				$res['res'] = false;
				$res['type'] = 'error';
				echo $res['msg'] = $e->getMessage();
				exit;
			}

			return $res;

		}

		public function createUserFromCoordinator($coordinatorId, $createdByUserId='') {


	    	try {

	    		$db = $this->getAdapter();
	    		$db->beginTransaction();

	        	if (empty($coordinatorId)) {
					throw new Exception('empty-coordinator-id');
				}

				$coordinatorsObj = new Default_Model_DbTable_Coordinators();
	            $coordinator = $coordinatorsObj->getRow($coordinatorId);
				if (!$coordinator->coordinator_id) {
					throw new Exception('coordinator-record-not-found');
				}
				if (!$coordinator->email1) {
					throw new Exception('coordinator-does-not-have-email-address');
				}
				if (!filter_var($coordinator->email1, FILTER_VALIDATE_EMAIL)) {
					throw new Exception('coordinator-does-not-have-a-valied-email-address');
				}

				$validator = new Zend_Validate_Db_NoRecordExists(array('table' => 'users', 'field' => 'usermail'));
				if (!$validator->isValid($coordinator->email1)) {
					throw new Exception('user-already-exists-with-this-email');
				}

				switch ($coordinator->local_or_foreign) {
					case 'local':
                        $mailLanguage = 'en';
						$tmpUserRole = 'local-coordinator';
					break;
					case 'foreign'://anlasmalardaki karsi kurum koordinatorleri
                        $mailLanguage = 'en';
                        $tmpUserRole = 'foreign-institution';

						$tmpUniversityId = '';
                        if ($coordinator->university_id) {
                            $tmpUniversityId = $coordinator->university_id;
                        } else {
                            $coordinatorsUnitsObj = new Default_Model_DbTable_CoordinatorsUnits();
                            $coordinatorAssignment = $coordinatorsUnitsObj->getRow($coordinator->coordinator_id);
                            if ($coordinatorAssignment->coordinator_id) {
                                $tmpUniversityId = $coordinatorAssignment->university_id;
                            }
                        }
                        if (!$tmpUniversityId) {
                            throw new Exception('Coordinator does not have any assignment yet!');
                        }


					break;

					case '':
					default:
						$tmpUserRole = 'member';
					break;
				}
                //echo $tmpUserRole;exit;

	            //$newUser = $model->createRow();
                $newUser = new stdClass();
	            $newUser->firstname = $coordinator->firstname;
	            $newUser->lastname = $coordinator->lastname;
	            $newUser->usermail = $coordinator->email1;
	            $newUser->gender_id = 0;
	            $newUser->is_active = '0';
	            $newUser->is_deleted = '0';
	            $newUser->role = $tmpUserRole;
	            $newUser->university_id = $tmpUniversityId;
	            $newUser->usertype = 'institution';
	            $newUser->activation_code = $this->generateActivationCode($coordinator->email1);
	            $tmpNewPassword = $this->generatePassword($coordinator->email1);
	            $newUser->userpass = md5($tmpNewPassword);
	            $newUser->create_datetime = date('Y-m-d H:i:s');
	            $newUser->create_ip = $_SERVER['REMOTE_ADDR'];
	            $newUserID = $this->addUser((array)$newUser);

	            $newUser->user_id = $newUserID;
            	$this->sendNewUserWelcomeMail($newUser, $tmpNewPassword, $mailLanguage, $createdByUserId);

	            //$coordinator->account_created='1';
	            //$coordinator->user_id = $newUserID;
	            //$coordinator->save();



		 		$db->commit();


	    	} catch (Exception $e)
	    	{
	    		$db->rollBack();
				throw $e;
			}


		}

		public function createUserFromUniversityWILLBEDELETED($id, $createdByUserId) {


    		$db = $this->getAdapter();
    		$db->beginTransaction();

	    	try {

	        	if (empty($id)) {
					throw new Exception('empty-university-id');
				}

				$universityObj = new Default_Model_DbTable_University();
				$university = $universityObj->getUniversity($id);

	        	if (empty($university->email)) {
					throw new Exception('empty-university-email');
				}

				$validator = new Zend_Validate_Db_NoRecordExists(array('table' => 'users', 'field' => 'usermail'));
				if (!$validator->isValid($university->email)) {
					throw new Exception('duplicate-user-account-with-email');
				}



	            //$newUser = $model->createRow();
	            $newUser->firstname = $university->university_label_en;
	            $newUser->lastname = $university->erasmus_code;
	            $newUser->usermail = $university->email;
	            $newUser->gender_id = '0';
	            $newUser->is_active = '0';
	            $newUser->is_deleted = '0';
	            $newUser->role = 'foreign-institution';
	            $newUser->usertype = 'institution';
	            $newUser->activation_code = $this->generateActivationCode($university->email);
	            $tmpNewPassword = $this->generatePassword($university->email);
	            $newUser->userpass = md5($tmpNewPassword);
	            $newUser->create_datetime = date('Y-m-d H:i:s');
	            $newUser->create_ip = $_SERVER['REMOTE_ADDR'];
	            $newUserID = $this->addUser((array)$newUser);

	            $newUser->user_id = $newUserID;

	            $university->user_id = $newUserID;
	            $university->save();

	            $this->sendNewUserWelcomeMail($newUser, $tmpNewPassword, 'en', $createdByUserId);

		 		$db->commit();

		 		return true;


	    	} catch (Exception $e) {
	    		$db->rollBack();
				throw new Exception($e->getMessage());
			}

		}


	    public function getUserTypes() {

    		try {
    			return array(
					'individual'=>'individual',
					'institution'=>'institution',
    			);

    		} catch (Exception $e) {
    			throw $e;
    		}
	    }

	    public function getUserGroups() {

    		try {
    			return array(
					''=>'--',
					'administration'=>'administration',
    			);

    		} catch (Exception $e) {
    			throw $e;
    		}
	    }


	    public function doCoordinatorLoginOps($authData) {

    		try {
                if ( ($authData->role == 'local-coordinator') ) {
                    $authData->role = 'local-coordinator-no-units';

                    $coordinatorsObj = new Default_Model_DbTable_Coordinators();
                    $coordinator = $coordinatorsObj->getRowByFilter(array('local_or_foreign'=>'local', 'email1'=>$authData->usermail, 'is_active'=>'1'));
                    if ($coordinator->coordinator_id) {

                        $authData->is_coordinator 	= true;
                        $authData->coordinator 		= (object)$coordinator->toArray();


                        $coordinatorsUnitsObj = new Default_Model_DbTable_CoordinatorsUnits();
                        //$coordinatorsAssignments = $coordinatorsUnitsObj->getRowsAsPairs('faculty_id', 'department_id', array('coordinator_id'=>$coordinator->coordinator_id));
                        $coordinatorsAssignments = $coordinatorsUnitsObj->getRows(array('coordinator_id'=>$coordinator->coordinator_id), array(), true);
                        //print_r($coordinatorsAssignments);exit;
                        if ($coordinatorsAssignments) {

                            $departmentsObj = new Default_Model_DbTable_Department();

                            $coordinatorUnits = array();
                            $coordinatorFaculties = array();
                            $coordinatorDepartments = array();
                            foreach ($coordinatorsAssignments as $assignment) {
                                $assignment = (object)$assignment;

                                if ($assignment->unit_type == 'faculty') {
                                    $coordinatorFaculties[$assignment->faculty_id] = $assignment->faculty_id;
                                    $departments = $departmentsObj->getDepartmentsAsPairs($assignment->faculty_id);

                                    foreach ($departments as $departmentId=>$departmentCode) {
                                        $coordinatorUnits[$assignment->faculty_id][$departmentId] = $departmentId;
                                        $coordinatorDepartments[$departmentId] = $departmentId;
                                    }

                                } else if ($assignment->unit_type == 'department') {
                                    $coordinatorUnits[$assignment->faculty_id][$assignment->department_id] = $assignment->department_id;
                                    $coordinatorFaculties[$assignment->faculty_id] = $assignment->faculty_id;
                                    $coordinatorDepartments[$assignment->department_id] = $assignment->department_id;

                                }
                            }

                            if ($coordinatorUnits) {

                                $authData->role = 'local-coordinator';

                                $authData->coordinatorUnits 		= $coordinatorUnits;
                                $authData->coordinatorFaculties 	= array_keys($coordinatorFaculties);
                                $authData->coordinatorDepartments 	= array_keys($coordinatorDepartments);

                            }

                        }
                    }
                }

                return $authData;

    		} catch (Exception $e) {
    			throw $e;
    		}
	    }


	}